<?php
namespace util;

//来自http://www.codesec.net/view/485087.html

/**
 * 数据库操作类
 *@example
 *$mysql = MysqlConnect::getIntance();
 *$obj = $mysql->queryObject("SELECT * FROM article where id=40");  //查询对象
 *$list = $mysql->queryList("SELECT * FROM article");   //查询列表
 *$count = $mysql->queryInt("SELECT COUNT(0) FROM article");    //查询数量
 *
 *
 *$data = array();
 *$data['article_title'] = "haha";
 *$count = $mysql->update("article", $data, "id=40");   //修改
 *
 *
 *$data = array();
 *$data['article_title'] = "haha";
 *$count = $mysql->add("article", $data);   //新增
 *
 *
 *$result = $mysql->delete("article", "id=40"); //删除
 *
 *$result = $mysql->deleteAll("article"); //删除全部
 *
 *$mysql->close();  //关闭数据库连接
 */

class MysqlConnect{
    //私有的属性
    private static $dbcon = false;
    private $host;
    private $port;
    private $user;
    private $pass;
    private $db;
    private $charset;
    private $link;
    //私有的构造方法
    private function __construct($config=array()){
        $this->host = $config['host'] ? $config['host'] : 'localhost';
        $this->port = $config['port'] ? $config['port'] : '3306';
        $this->user = $config['user'] ? $config['user'] : 'root';
        $this->pass = $config['pass'] ? $config['pass'] : '';
        $this->db = $config['db'] ? $config['db'] : 'spider';
        $this->charset = isset($config['charset']) ? $config['charset'] : 'utf8';
        //连接数据库
        $this->db_connect();
        //选择数据库
        $this->db_usedb();
        //设置字符集
        $this->db_charset();
    }
    
    //连接数据库
    private function db_connect(){
        $this->link=mysqli_connect($this->host.':'.$this->port,$this->user,$this->pass);
        if(!$this->link){
            CommonUtil::echoStr("\n数据库连接失败\n");
            CommonUtil::echoStr("\n错误编码：".mysqli_errno($this->link)."\n");
            CommonUtil::echoStr("\n错误信息：".mysqli_error($this->link)."\n");
            exit;
        }
    }
    //设置字符集
    private function db_charset(){
        mysqli_query($this->link,"set names {$this->charset}");
    }
    //选择数据库
    private function db_usedb(){
        mysqli_query($this->link,"use {$this->db}");
    }
    //私有的克隆
    private function __clone(){
        die('clone is not allowed');
    }
    //公用的静态方法
    public static function getIntance(){
        if(self::$dbcon==false){
            self::$dbcon=new self;
        }
        return self::$dbcon;
    }
    //执行sql语句的方法
    public function query($sql){
        $res = mysqli_query($this->link,$sql);
        if(!$res){
            
            CommonUtil::fwrite('sql语句执行失败：'.date('Y-m-d H:i:s'),'a');
            CommonUtil::fwrite('错误SQL语句：'.$sql,'a');
            CommonUtil::fwrite("错误编码是".mysqli_errno($this->link),'a');
            CommonUtil::fwrite("错误信息是".mysqli_error($this->link),'a');
            CommonUtil::fwrite('','a');
            CommonUtil::fwrite('----------------------------','a');
            CommonUtil::fwrite('','a');
            
            return false;
            
        }
        return $res;
    }
    //打印数据
    public function p($arr){
        echo "<pre>";
        print_r($arr);
        echo "</pre>";
    }
    public function v($arr){
        echo "<pre>";
        var_dump($arr);
        echo "</pre>";
    }
    //获得最后一条记录id
    public function getInsertid(){
        return mysqli_insert_id($this->link);
    }
    
    //获取一条记录,前置条件通过资源获取一条记录
    public function getFormSource($query,$type="assoc"){
        if(!in_array($type,array("assoc","array","row")))
        {
            die("mysqli_query error");
        }
        $funcname="mysqli_fetch_".$type;
        return $funcname($query);
    }
    
    /**
     * 查询数量，如聚合查询SELECT COUNT(0) FROM article
     * @param
     * @return int / false
     */
    public function queryInt($sql){
        $query = $this->query($sql);
        
        if($query === false){
            return false;
        }
        
        $res = $query->fetch_row();
        if (is_array($res)){
            return $res[0];
        }else{
            return null;
        }
    
    }
    
    /**
     * 获取多条数据
     * @param string $sql   SQL语句
     * @return List / false
     */
    public function queryList($sql){
        $query=$this->query($sql);
        
        if($query === false){
            return false;
        }
        
        $list=array();
        while ($r=$this->getFormSource($query)) {
            $list[]=$r;
        }
        return $list;
    }
    
    /**
     * 获取一行记录
     * @param string $sql
     * @param string $type
     * @return object(array方式) / false
     */
    public function queryObject($sql,$type="assoc"){
        $query=$this->query($sql);
        
        if($query === false){
            return false;
        }
        
        if(!in_array($type,array("assoc",'array',"row"))){
            die("mysqli_query error");
        }
        $funcname="mysqli_fetch_".$type;
        return $funcname($query);
    }
    
    /**
     * 添加数据
     * @param string $table 表名
     * @param string orarray $data [数据]
     * @return int(最新添加的id) / false
     */
    public function add($table,$data){
        //遍历数组，得到每一个字段和字段的值
        $key_str='';
        $v_str='';
        foreach($data as $key=>$v){
            /* if(empty($v)){
                die("error");
            } */
            //$key的值是每一个字段s一个字段所对应的值
            $key_str.=$key.',';
            $v_str.="'$v',";
        }
        $key_str=trim($key_str,',');
        $v_str=trim($v_str,',');
        //判断数据是否为空
        $sql="insert into $table ($key_str) values ($v_str)";
        $res = $this->query($sql);
        
        if($res === false){
            return false;
        }
        
        //返回上一次增加操做产生ID值
        return $this->getInsertid();
    }
    
    /**
     * 修改数据
     * @param [type] $table [表名]
     * @param [type] $data [数据]
     * @param [type] $where [条件]
     * @return int(影响的行数) / false
     */
    public function update($table,$data,$where){
        //遍历数组，得到每一个字段和字段的值
        $str='';
        foreach($data as $key=>$v){
            $str.="$key='$v',";
        }
        $str=rtrim($str,',');
        //修改SQL语句
        $sql="update {$table} set {$str} where {$where}";
        $res = $this->query($sql);
        
        if($res === false){
            return false;
        }
        
        //返回受影响的行数
        return mysqli_affected_rows($this->link);
    }
    
    /**
     * 删除数据
     * @param string $table  表名
     * @param string $condition   删除条件   $condition = 'id in(1,2,3)'
     * @return int(影响的行数) / false
     */
    public function delete($table, $condition){
    
        $sql = "delete from {$table} where {$condition}";
        $res = $this->query($sql);
        
        if($res === false){
            return false;
        }
        
        //返回受影响的行数
        return mysqli_affected_rows($this->link);
    }
    
    /**
     * 删除全部数据
     * @param string $table  表名
     * @param string $condition   删除条件   $condition = 'id in(1,2,3)'
     * @return int(影响的行数) / false
     */
    public function deleteAll($table, $condition){
        return $this->delete($table, '1=1');
    }
    
    //关闭当前数据库连接
    public function close(){
        $this->link->close();
    }
    
}
?>